/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dictionary.dal;

import dictionary.entities.Classify;
import dictionary.entities.Mean;
import dictionary.entities.Word;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author s0c0labd
 */
public class MeanDAO {

    private Connection cn;
    private WordDAO wDAO;
    private ClassifyDAO cDAO;

    public MeanDAO() {
    }

    public List<Mean> getByWord(Word w) {

        cn = ConnectDB.getConnection();
        List<Mean> l = new ArrayList<Mean>();
        CallableStatement cstm1;
        try {
            cstm1 = cn.prepareCall("{call spGetWordID(?,?,?)}");
            cstm1.registerOutParameter(3, Types.INTEGER);
            cstm1.setInt(2, w.getType());
            cstm1.setString(1, w.getWord());
            cstm1.execute();
            int wordID = cstm1.getInt(3);
            cstm1.close();

            if (wordID > 0) {
                PreparedStatement pstm = cn.prepareStatement("SELECT * FROM Mean WHERE WordID = ?");
                pstm.setInt(1, wordID);
                ResultSet rs = pstm.executeQuery();
                while (rs.next()) {
                    CallableStatement cstm2 = cn.prepareCall("{call spGetClassifyName(?,?,?)}");
                    cstm2.registerOutParameter(2, Types.NVARCHAR);
                    cstm2.registerOutParameter(3, Types.INTEGER);
                    cstm2.setInt(1, rs.getInt("ClassifyID"));
                    cstm2.execute();
                    int i = cstm2.getInt(3);
                    if (i == 0) {
                        String s = cstm2.getString(2);
                        l.add(new Mean(w, new Classify(s), rs.getString("Mean")));
                    }
                    cstm2.close();
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(MeanDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    public Mean getByMean(Word w, Classify c) {
        cn = ConnectDB.getConnection();
        Mean m = new Mean();
        if (w.getType() != c.getType()) {
            m = null;
        } else {
            CallableStatement cstm1;
            try {
                cstm1 = cn.prepareCall("{call spGetWordID(?,?,?)}");
                cstm1.registerOutParameter(3, Types.INTEGER);
                cstm1.setString(1, w.getWord());
                cstm1.setInt(2, w.getType());
                cstm1.execute();
                int wordID = cstm1.getInt(3);
                cstm1.close();
                cstm1 = cn.prepareCall("{call spGetClassifyID(?,?,?)}");
                cstm1.registerOutParameter(3, Types.INTEGER);
                cstm1.setString(1, c.getClassify());
                cstm1.setInt(2, w.getType());
                cstm1.execute();
                int ClassifyID = cstm1.getInt(3);
                cstm1.close();

                if (wordID != -1 && ClassifyID != -1) {
                    PreparedStatement pstm = cn.prepareStatement("SELECT Mean FROM Mean WHERE WordID = ? AND ClassifyID =?");
                    pstm.setInt(1, wordID);
                    pstm.setInt(2, ClassifyID);
                    ResultSet rs = pstm.executeQuery();
                    while (rs.next()) {
                        m.setClassify(c);
                        m.setWord(w);
                        m.setMean(rs.getString("Mean"));
                    }
                }
                cn.close();
            } catch (SQLException ex) {
                Logger.getLogger(MeanDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return m;
    }

    public int insertMean(Word w, Classify c, String s) {
        cn = ConnectDB.getConnection();
        int rt = 5;
        if (w.getType() == c.getType()) {
            try {
                CallableStatement cstm1 = cn.prepareCall("{call spGetWordID(?,?,?)}");

                cstm1.registerOutParameter(3, Types.INTEGER);
                cstm1.setString(1, w.getWord());
                cstm1.setInt(2, w.getType());
                cstm1.execute();
                int wordID = cstm1.getInt(3);
                cstm1.close();
                CallableStatement cstm2 = cn.prepareCall("{call spGetClassifyID(?,?,?)}");
                cstm2.registerOutParameter(3, Types.INTEGER);
                cstm2.setString(1, c.getClassify());
                cstm2.setInt(2, w.getType());
                cstm2.execute();
                int ClassifyID = cstm2.getInt(3);
                cstm2.close();

                if (wordID > 0 && ClassifyID > 0) {
                    CallableStatement cstm = cn.prepareCall("{call spInsertMean(?,?,?,?)}");
                    cstm.registerOutParameter(4, Types.INTEGER);
                    cstm.setInt(1, wordID);
                    cstm.setInt(2, ClassifyID);
                    cstm.setString(3, s);
                    cstm.execute();
                    rt = cstm.getInt(4);
                    cstm.close();
                } else {
                    rt = 4;
                }
                cn.close();
            } catch (SQLException ex) {
                Logger.getLogger(WordDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
            rt = 6;
        }
        return rt;
    }

    public int deleteMean(Word w, Classify c) {
        cn = ConnectDB.getConnection();
        int rt = 4;
        if (w.getType() == c.getType()) {
            CallableStatement cstm1;
            try {
                cstm1 = cn.prepareCall("{call spGetWordID(?,?,?)}");
                cstm1.registerOutParameter(3, Types.INTEGER);
                cstm1.setString(1, w.getWord());
                cstm1.setInt(2, w.getType());
                cstm1.execute();
                int wordID = cstm1.getInt(3);
                cstm1.close();
                CallableStatement cstm2 = cn.prepareCall("{call spGetClassifyID(?,?,?)}");
                cstm2.registerOutParameter(3, Types.INTEGER);
                cstm2.setInt(2, w.getType());
                cstm2.setString(1, c.getClassify());
                cstm2.execute();
                int ClassifyID = cstm2.getInt(3);
                cstm2.close();
                if (wordID > 0 && ClassifyID > 0) {
                    CallableStatement cstm = cn.prepareCall("{call spDeleteMean(?,?,?)}");
                    cstm.registerOutParameter(3, Types.INTEGER);
                    cstm.setInt(1, wordID);
                    cstm.setInt(2, ClassifyID);
                    cstm.execute();
                    rt = cstm.getInt(3);
                    cstm.close();
                } else {
                    if (wordID > 0) {
                        rt = 2;
                    }
                    if (ClassifyID > 0) {
                        rt = 1;
                    }
                    if (wordID < 0 && ClassifyID < 0) {
                        rt = 3;
                    }
                }
                cn.close();
            } catch (SQLException ex) {
                Logger.getLogger(MeanDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
            rt = 5;
        }
        return rt;
    }

    public int updateMean(Word w, Classify c, String s) {
        cn = ConnectDB.getConnection();
        int rt = 2;
        try {
            CallableStatement cstm1 = cn.prepareCall("{call spGetWordID(?,?,?)}");
            cstm1.registerOutParameter(3, Types.INTEGER);
            cstm1.setString(1, w.getWord());
            cstm1.setInt(2, w.getType());
            cstm1.execute();
            int wordID = cstm1.getInt(3);
            cstm1.close();

            CallableStatement cstm2 = cn.prepareCall("{call spGetClassifyID(?,?,?)}");
            cstm2.registerOutParameter(3, Types.INTEGER);
            cstm2.setString(1, c.getClassify());
            cstm2.setInt(2, w.getType());
            cstm2.execute();
            int ClassifyID = cstm2.getInt(3);
            cstm2.close();

            if (wordID > 0 && ClassifyID > 0) {
                CallableStatement cstm = cn.prepareCall("{call spUpdateMean(?,?,?,?)}");
                cstm.registerOutParameter(4, Types.INTEGER);
                cstm.setInt(1, wordID);
                cstm.setInt(2, ClassifyID);
                cstm.setString(3, s);
                cstm.execute();
                rt = cstm.getInt(4);
                cstm.close();
            } else {
                if (wordID > 0) {
                    rt = 1;
                }
                if (ClassifyID > 0) {
                    rt = 1;
                }
                if (wordID < 0 && ClassifyID < 0) {
                    rt = 1;
                }
            }
            cn.close();
        } catch (SQLException ex) {
            Logger.getLogger(WordDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rt;
    }
}
